﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using NewDDSys.IDAL;
using NewDDSys.Entity;
using NewDDSys.Utility;
using System.Text;

namespace NewDDSys.ORADAL
{
    public class KfhbDAL:IKfhbDAL
    {
        public int AddKfhb(KfhbInfo kfhb)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, kfhb.Num),
                DALHelper.MakeInParam(":kday", OracleType.VarChar, kfhb.Kday),
                DALHelper.MakeInParam(":banzu", OracleType.VarChar, kfhb.Banzu),
                DALHelper.MakeInParam(":tianbao", OracleType.VarChar, kfhb.Tianbao),
                DALHelper.MakeInParam(":ku", OracleType.VarChar, kfhb.Ku),
                DALHelper.MakeInParam(":pinzhong", OracleType.VarChar, kfhb.Pinzhong),
                DALHelper.MakeInParam(":cl", OracleType.Number, kfhb.Cl)

            };
            return (DALHelper.RunCommand(@"insert into dd.kfhb (num, kday, banzu, tianbao, ku, pinzhong, cl) values (:num, to_date(:kday,'yyyy-mm-dd hh24:mi:ss'), :banzu, :tianbao, :ku, :pinzhong, :cl)", prams));
        }
        public int UpdateKfhb(string num, string pinzhong, decimal cl)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, num),
                DALHelper.MakeInParam(":pinzhong", OracleType.VarChar, pinzhong),
                DALHelper.MakeInParam(":cl", OracleType.Number, cl)
            };
            return (DALHelper.RunCommand(@"update dd.kfhb set pinzhong = :pinzhong, cl = :cl where num = :num", prams));

        }
        
        public int Del(string num)
        {
            OracleParameter[] prams = {
                
                DALHelper.MakeInParam(":num", OracleType.VarChar, num)                      
            };
            return (DALHelper.RunCommand(@"delete from dd.kfhb where NUM = :num", prams));
        }
        public DataSet Select(string num)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, num)
            };
            return (DALHelper.RunProcReturn(@"select * from dd.kfhb where NUM = :num", prams, "Sbynum"));
        }

        public DataSet SelectByDay(string sday, string ku)
        {
            string bday = sday + "00:00:00";
            string eday = sday + "23:59:59";
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bday", OracleType.VarChar, bday),
                DALHelper.MakeInParam(":eday", OracleType.VarChar, eday),
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku)
            };

            return (DALHelper.RunProcReturn(@"select * from dd.kfhb where kday >= to_date(:bday,'yyyy-mm-dd hh24:mi:ss') and kday <= to_date(:eday,'yyyy-mm-dd hh24:mi:ss') and ku = :ku", prams, "Sbyday"));
        
        }
        public decimal rkcl(string cday, string ku, string pinzhong)
        {
            string btime = "";
            string etime = "";
            etime = cday + "08:00:00";
            DateTime jstime = Convert.ToDateTime(cday).AddHours(8);
            DateTime kstime = jstime.AddDays(-1);
            btime = kstime.ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku),
                DALHelper.MakeInParam(":pinzhong", OracleType.VarChar, pinzhong),
                DALHelper.MakeInParam(":btime", OracleType.VarChar, btime),
                DALHelper.MakeInParam(":etime", OracleType.VarChar, etime)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(cl) from dd.kfhb where ku = :ku and pinzhong = :pinzhong and kday >= to_date(:btime,'yyyy-mm-dd hh24:mi:ss') and kday <= to_date(:etime,'yyyy-mm-dd hh24:mi:ss')", prams);
            return Convert.ToDecimal(obj.ToString());
        }
        public string AUTOID(string ku)
        {

            string num = "";
            object time = DALHelper.ExecuteSQLScalar(@"select sysdate from dual");
            string kudm = "";
            switch (ku)
            {
                case "嘉东库":
                    kudm = "JD"; break;
                case "板材库":
                    kudm = "BC"; break;
                case "不锈钢热轧库":
                    kudm = "BXR"; break;
                case "不锈钢冷轧库":
                    kudm = "BXL"; break;
                case "碳钢热轧库":
                    kudm = "TR"; break;
                case "碳钢冷轧库":
                    kudm = "TL"; break;
                case "一高线":
                    kudm = "1G"; break;
                case "二高线":
                    kudm = "2G"; break;
                case "大棒线":
                    kudm = "DB"; break;
                case "四轧库":
                    kudm = "SZ"; break;
                case "酸洗库":
                    kudm = "SX"; break;
                case "化产库":
                    kudm = "HC"; break;
                default:
                    break;
            }
            num = "kfhb" + kudm + time.ToString();


            return num;
        }
        public string pzrk(string cday, string pinzhong)
        {
            string et = cday + "08:00:00";
            string bt = Convert.ToDateTime(cday).AddHours(-16).ToString();
            OracleParameter[] prams = {
                
                DALHelper.MakeInParam(":pinzhong", OracleType.VarChar, pinzhong),
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(cl)  from dd.kfhb where pinzhong = :pinzhong and kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams);
            return obj.ToString();
        }
        
    }
}
